package org.zjvis.datascience.service;

import com.alibaba.fastjson.JSONObject;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.zjvis.datascience.common.constant.DatabaseConstant;
import org.zjvis.datascience.common.dto.DatasetAndCategoryDTO;
import org.zjvis.datascience.common.dto.DatasetCategoryDTO;
import org.zjvis.datascience.common.dto.DatasetDTO;
import org.zjvis.datascience.common.dto.DatasetProjectDTO;
import org.zjvis.datascience.common.dto.ProjectDTO;
import org.zjvis.datascience.common.dto.dataset.DatasetNameStatusDTO;
import org.zjvis.datascience.common.dto.dataset.DatasetNameTypeDTO;
import org.zjvis.datascience.common.dto.dataset.DatasetUsedInProjectDTO;
import org.zjvis.datascience.common.dto.user.UserDTO;
import org.zjvis.datascience.common.exception.BaseErrorCode;
import org.zjvis.datascience.common.exception.DataScienceException;
import org.zjvis.datascience.common.util.JwtUtil;
import org.zjvis.datascience.common.util.SqlUtil;
import org.zjvis.datascience.common.util.db.JDBCUtil;
import org.zjvis.datascience.common.vo.category.DatasetAndCategoryForProjectVO;
import org.zjvis.datascience.common.vo.category.DatasetAndCategoryVO;
import org.zjvis.datascience.common.vo.dataset.QueryDatasetUsedInProjectVO;
import org.zjvis.datascience.service.dataprovider.GPDataProvider;
import org.zjvis.datascience.service.mapper.DatasetCategoryMapper;
import org.zjvis.datascience.service.mapper.DatasetMapper;
import org.zjvis.datascience.service.mapper.DatasetProjectMapper;

/**
 * @description DatasetCategory 数据集分类 Service
 * @date 2021-12-23
 */
@Service
public class DatasetCategoryService {

    private final static Logger logger = LoggerFactory.getLogger("DatasetCategoryService");

    @Autowired
    private DatasetCategoryMapper datasetCategoryMapper;

    @Autowired
    private DatasetProjectMapper datasetProjectMapper;

    @Autowired
    private DatasetMapper datasetMapper;

    @Autowired
    private GPDataProvider gpDataProvider;

    @Autowired
    private DatasetProjectService datasetProjectService;

    @Autowired
    private ProjectService projectService;

    static final String defaultCategory = "默认分类";

    /**
     * 根据用户id获取分类目录
     *
     * @param userId
     * @return
     */
    public List<DatasetCategoryDTO> queryByUserId(Long userId) {
        List<DatasetCategoryDTO> datasetCategoryList = datasetCategoryMapper.queryByUserId(userId);
        return datasetCategoryList;
    }

    /**
     * 根据用户id获取分类目录及其下面的数据集
     *
     * @param userId
     * @return
     */
    public List<DatasetAndCategoryVO> queryDatasetAndCategoryByUserId(Long userId) {
        List<DatasetAndCategoryVO> dcvList = new ArrayList<>();
        List<DatasetAndCategoryDTO> datasetCategoryList = datasetCategoryMapper
            .queryDatasetAndCategoryByUserId(userId);
        if (datasetCategoryList.isEmpty()) {
            // 空分类，加入默认分类
            UserDTO user = JwtUtil.getCurrentUserDTO();

            DatasetCategoryDTO dcDTO = new DatasetCategoryDTO();
            dcDTO.setName(defaultCategory);
            dcDTO.setUserId(user.getId());
            dcDTO.setGmtCreator(user.getId());
            dcDTO.setGmtModifier(user.getId());
            Long cateId = save(dcDTO);
            DatasetAndCategoryVO datasetVo = new DatasetAndCategoryVO();
            datasetVo.setCategoryId(cateId);
            datasetVo.setCategoryName(dcDTO.getName());
            List<DatasetNameTypeDTO> dataset = new ArrayList<>();

            datasetVo.setDataset(dataset);
            dcvList.add(datasetVo);
            return dcvList;
        }

        Set<Long> categoryIds = new HashSet<>();
        for (DatasetAndCategoryDTO dc : datasetCategoryList) {
            if (categoryIds.contains(dc.getCategoryId())) {
                for (DatasetAndCategoryVO dacDto : dcvList) {
                    if (dacDto.getCategoryId().equals(dc.getCategoryId())) {
                        DatasetNameTypeDTO dnDto = new DatasetNameTypeDTO();
                        dnDto.setId(dc.getDatasetId());
                        dnDto.setName(dc.getDatasetName());
                        try {
                            JSONObject dataJson = JSONObject.parseObject(dc.getDataJson());
                            dnDto.setType(dataJson.getString("type"));
                        } catch (Exception e) {
                            logger.error(e.getMessage());
                        }
                        dacDto.getDataset().add(dnDto);
                    }
                }
            } else {
                categoryIds.add(dc.getCategoryId());
                DatasetAndCategoryVO datasetVo = new DatasetAndCategoryVO();
                datasetVo.setCategoryId(dc.getCategoryId());
                datasetVo.setCategoryName(dc.getCategoryName());

                List<DatasetNameTypeDTO> dataset = new ArrayList<>();
                DatasetNameTypeDTO dnDto = new DatasetNameTypeDTO();

                if (dc.getDatasetId() != null || !StringUtils.isBlank(dc.getDatasetName())) {
                    dnDto.setId(dc.getDatasetId());
                    dnDto.setName(dc.getDatasetName());

                    try {
                        JSONObject dataJson = JSONObject.parseObject(dc.getDataJson());
                        dnDto.setType(dataJson.getString("type"));
                    } catch (Exception e) {
                        logger.error(e.getMessage());
                    }
                    dataset.add(dnDto);
                }
                datasetVo.setDataset(dataset);
                dcvList.add(datasetVo);
            }
        }
        return dcvList;
    }


    public Long save(DatasetCategoryDTO dcDTO) {
        datasetCategoryMapper.save(dcDTO);
        return dcDTO.getId();
    }

    public Boolean update(DatasetCategoryDTO dcDTO) {
        try {
            datasetCategoryMapper.update(dcDTO);
        } catch (Exception e) {
            return false;
        }
        return true;
    }

    @Transactional(rollbackFor = Exception.class)
    public JSONObject delete(DatasetCategoryDTO dc) throws Exception {
        JSONObject resData = new JSONObject();
        resData.put("error", 0);

        /* 先判断分类下的数据集中是否有project在用 */
        List<DatasetDTO> datasets = datasetMapper.queryByCategoryId(dc.getId());
        
        checkDelete(datasets);

        /* 删除分类目录 */
        int res = datasetCategoryMapper.delete(dc);
        if (res > 0) {
            List<String> sqlList = new ArrayList<>();
            for (DatasetDTO dataset : datasets) {
                /* 删除分类目录下的数据集 */
                datasetMapper.delete(dataset);

                /* 生成删除GP中数据集源表sql */
                String json = dataset.getDataJson();
                JSONObject jo = JSONObject.parseObject(json);
                String schema = jo.getString("schema");
                String table = jo.getString("table");
                String newTable = schema + "." + SqlUtil.formatPGSqlColName(table);
                sqlList.add(String.format(DatabaseConstant.SQL_DROP_TABLE, newTable));
            }

            /* 删除GP中数据集源表 */
            Connection con = null;
            Statement statement = null;
            if (!sqlList.isEmpty()) {
                try {
                    con = gpDataProvider.getConn(1L);
                    con.setAutoCommit(false);
                    statement = con.createStatement();
                    for (String sql : sqlList) {
                        logger.info(sql);
                        statement.addBatch(sql);
                    }
                    statement.executeBatch();
                    con.commit();
                } catch (Exception e) {
                    logger.error(e.getMessage());
                    try {
                        con.rollback();
                    } catch (SQLException e1) {
                        logger.error(e1.getMessage());
                    }
                    throw new DataScienceException(BaseErrorCode.DATASET_GP_DELETE_ERROR);
                } finally {
                    JDBCUtil.close(con, statement, null);
                }
            }
        }

        resData.put("result", true);
        return resData;
    }

    public List<DatasetAndCategoryForProjectVO> queryDatasetCategoryByProjectId(Long projectId) {
        JSONObject data = new JSONObject();
        data.put("userId", JwtUtil.getCurrentUserId());
        data.put("projectId", projectId);

        List<DatasetAndCategoryDTO> datasetCategoryList = datasetCategoryMapper
            .queryDatasetCategoryByProjectId(data);

        return handle(datasetCategoryList);
    }

    public List<DatasetAndCategoryForProjectVO> searchInProject(Long projectId, String content) {
        JSONObject data = new JSONObject();
        data.put("userId", JwtUtil.getCurrentUserId());
        data.put("projectId", projectId);
        data.put("content", content);

        List<DatasetAndCategoryDTO> datasetCategoryList = datasetCategoryMapper
            .queryDatasetCategoryByProjectId(data);

        return handle(datasetCategoryList);
    }

    private List<DatasetAndCategoryForProjectVO> handle(
        List<DatasetAndCategoryDTO> datasetCategoryList) {
        List<DatasetAndCategoryForProjectVO> dcvList = new ArrayList<>();

        if (datasetCategoryList == null || datasetCategoryList.isEmpty()) {
            return dcvList;
        }
        Set<Long> categoryIds = new HashSet<>();
        for (DatasetAndCategoryDTO dc : datasetCategoryList) {
            if (categoryIds.contains(dc.getCategoryId())) {
                for (DatasetAndCategoryForProjectVO dacDto : dcvList) {
                    if (dacDto.getCategoryId().equals(dc.getCategoryId())) {
                        DatasetNameStatusDTO dnDto = new DatasetNameStatusDTO();
                        dnDto.setId(dc.getDatasetId());
                        dnDto.setName(dc.getDatasetName());
                        dnDto.setStatus(dc.getStatus());
                        dacDto.getDataset().add(dnDto);
                    }
                }
            } else {
                categoryIds.add(dc.getCategoryId());
                DatasetAndCategoryForProjectVO datasetVo = new DatasetAndCategoryForProjectVO();
                datasetVo.setCategoryId(dc.getCategoryId());
                datasetVo.setCategoryName(dc.getCategoryName());

                List<DatasetNameStatusDTO> dataset = new ArrayList<>();
                DatasetNameStatusDTO dnDto = new DatasetNameStatusDTO();

                if (dc.getDatasetId() != null || !StringUtils.isBlank(dc.getDatasetName())) {
                    dnDto.setId(dc.getDatasetId());
                    dnDto.setName(dc.getDatasetName());
                    dnDto.setStatus(dc.getStatus());
                    dataset.add(dnDto);
                }
                datasetVo.setDataset(dataset);
                dcvList.add(datasetVo);
            }
        }
        return dcvList;
    }

    public void checkAuth(long categoryId) {
        long userId = JwtUtil.getCurrentUserId();
        //单纯数据集分类接口仅拥有者可以读写
        if (datasetCategoryMapper.checkAuth(categoryId, userId) == 0) {
            throw DataScienceException.of(BaseErrorCode.UNAUTHORIZED, "数据集分类id:" + categoryId);
        }
    }

    /**
     * 检查数据集使用情况
     * 
     * @param datasets
     * @throws Exception
     */
    public void checkDelete(List<DatasetDTO> datasets) throws Exception {
        if (datasets == null) {
            return;
        }

        StringBuffer sb = new StringBuffer();
        for (DatasetDTO dataset : datasets) {
            QueryDatasetUsedInProjectVO vo = new QueryDatasetUsedInProjectVO();
            vo.setDatasetId(dataset.getId());
            vo.setUserId(dataset.getUserId());

            List<DatasetUsedInProjectDTO> dtos = datasetProjectMapper.queryDatasetUsedInProject(vo);
            
            if (dtos != null && !dtos.isEmpty()) {
                for (DatasetUsedInProjectDTO dto : dtos) {
                    try {
                        Set<Long> users = datasetProjectService
                            .getDatasetUsedInProjectUser(dto.getProjectId(), dataset.getId());
                        if (!users.isEmpty()) {
                            users.remove(JwtUtil.getCurrentUserId());
                            if (users.size() > 0) {
                                sb.append("数据集：").append(dataset.getName())
                                    .append(" 正在被其他用户使用，请先对数据集进行转移!  ");
                            } else {
                                ProjectDTO project = projectService.query(dto.getProjectId());
                                sb.append("数据集：").append(dataset.getName()).append(" 在 ")
                                    .append(project.getName()).append(" 项目中使用，请先从项目中移出再删除!  ");
                            }
                        }
                    } catch (Exception e) {
                        logger.error(e.getMessage(), e);
                    }
                }
            }
            
        }
        if (StringUtils.isNotBlank(sb)) {
            throw new Exception(sb.toString());
        }
    }
}
